﻿/*
Ngày 2014-04-13: Tạo các bảng dùng cho System Log
*/

CREATE TABLE [dbo].[tblAction](
	[ID_Action] [int] NOT NULL,
	[ActionName] [nvarchar](20) NULL,
 CONSTRAINT [PK_tblAction] PRIMARY KEY CLUSTERED 
(
	[ID_Action] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

CREATE TABLE [dbo].[tblLog](
	[ID_Log] [int] IDENTITY(1,1) NOT NULL,
	[ID_Action] [int] NULL,
	[LogContent] [nvarchar](max) NULL,
	[LogDate] [datetime] NULL,
	[ID_User] [smallint] NOT NULL,
	[Ip] [nvarchar](20) NULL,
	[HostName] [nvarchar](100) NULL,
 CONSTRAINT [PK_tblLog] PRIMARY KEY CLUSTERED 
(
	[ID_Log] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[tblLog]  WITH CHECK ADD  CONSTRAINT [FK_tblLog_tblAction] FOREIGN KEY([ID_Action])
REFERENCES [dbo].[tblAction] ([ID_Action])
GO

ALTER TABLE [dbo].[tblLog] CHECK CONSTRAINT [FK_tblLog_tblAction]
GO

ALTER TABLE [dbo].[tblLog]  WITH CHECK ADD  CONSTRAINT [FK_tblLog_tblUser] FOREIGN KEY([ID_USER])
REFERENCES [dbo].[tblUser] ([USER_ID])
GO

ALTER TABLE [dbo].[tblLog] CHECK CONSTRAINT [FK_tblLog_tblUser]

go

INSERT [dbo].[tblAction] ([ID_Action], [ActionName]) VALUES (1, N'Thêm mới')
go
INSERT [dbo].[tblAction] ([ID_Action], [ActionName]) VALUES (2, N'Sửa')
go
INSERT [dbo].[tblAction] ([ID_Action], [ActionName]) VALUES (3, N'Xóa')
go
INSERT [dbo].[tblAction] ([ID_Action], [ActionName]) VALUES (4, N'Đăng nhập')
go
INSERT [dbo].[tblAction] ([ID_Action], [ActionName]) VALUES (5, N'Đăng xuất')
go
INSERT [dbo].[tblAction] ([ID_Action], [ActionName]) VALUES (6, N'Xác nhận')
go
INSERT [dbo].[tblAction] ([ID_Action], [ActionName]) VALUES (7, N'Hủy xác nhận')

--=================================================
--Thêm vào tblMenu
go
INSERT [dbo].[tblMENU] ([Menu_ID], [MenuName], [VBMenuName]) VALUES (N'107', N'Nhật ký sử dụng hệ thống', N'SystemLogToolStripMenuItem')
--=================================================
--Thêm vào tblNhom_Menu
go
INSERT [dbo].[tblNHOM_MENU] ([Nhom_ID], [Menu_ID]) VALUES (1, N'107')
go
INSERT [dbo].[tblNHOM_MENU] ([Nhom_ID], [Menu_ID]) VALUES (2, N'107')
go
INSERT [dbo].[tblNHOM_MENU] ([Nhom_ID], [Menu_ID]) VALUES (3, N'107')
go
INSERT [dbo].[tblNHOM_MENU] ([Nhom_ID], [Menu_ID]) VALUES (4, N'107')
go
INSERT [dbo].[tblNHOM_MENU] ([Nhom_ID], [Menu_ID]) VALUES (5, N'107')

--=================================================
--Ngày 2014-05-03: Thêm view để hiển thị
go
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[viewHT_Log]'))
DROP VIEW [dbo].[viewHT_Log]
GO

CREATE VIEW [dbo].[viewHT_Log]
AS
SELECT     dbo.tblLog.ID_Log, dbo.tblLog.ID_Action, dbo.tblAction.ActionName, dbo.tblLog.LogContent, dbo.tblLog.LogDate, dbo.tblLog.ID_User, dbo.tblUSER.loginname, 
                      dbo.tblUSER.fullname, dbo.tblLog.Ip, dbo.tblLog.HostName
FROM         dbo.tblAction INNER JOIN
                      dbo.tblLog ON dbo.tblAction.ID_Action = dbo.tblLog.ID_Action INNER JOIN
                      dbo.tblUSER ON dbo.tblLog.ID_User = dbo.tblUSER.user_id
